SELECT   

  V_UE_GSE.MESE_RIF,

  V_UE_GSE.ANNO_RIF,

  V_UE_GSE.PIVAUSSP,

  V_UE_GSE.CFUSSP,

  V_UE_GSE.CODICE_CONTRATTO,

  V_UE_GSE.POD,

  V_UE_GSE.CODSAPR,

  V_UE_GSE.ATT_ID,

  V_UE_GSE.TIPO_CLIENTE_FINALE,

  V_UE_GSE.TIPOTARIFFADOM,

  V_UE_GSE.TARIFFA_SOCIALE,

  V_UE_GSE.TIPOAPPROV,

  V_UE_GSE.DATADECORRENZA,

  V_UE_GSE.DATASCADENZA,

  V_UE_GSE.POTDISP,

  V_UE_GSE.POTIMP,

  V_UE_GSE.CONTRATTOSIGLATO,

  V_UE_GSE.TARMONOBI


                                                  from(

   SELECT TO_CHAR (con.con_dtiniges, 'mm') mese_rif,
          TO_CHAR (con.con_dtiniges, 'yyyy') anno_rif,
          sog.sog_pariva pivaussp, sog.sog_codfis cfussp,
          con.con_anno || con.con_id codice_contratto, ubi.att_idext pod,
          'S_' || ubi.att_idext codsapr, ubi.att_id,
          CASE
             WHEN (SELECT MAX (attval.catt_valvis)
                     FROM v_valcxatt attval
                    WHERE ubi.att_id = attval.att_id
                      AND attval.sta_idsys = 2
                      AND attval.catt_des = 'TIPO FORNITURA EE') =
                                                              'BASSA TENSIONE'
             AND con.tcon_des = 'DOMESTICO'
                THEN 'A'
             ELSE CASE
             WHEN (SELECT attval.catt_valvis
                     FROM v_valcxatt attval
                    WHERE ubi.att_id = attval.att_id
                      AND attval.sta_idsys = 2
                      AND attval.catt_des = 'TIPO FORNITURA EE') =
                                                              'BASSA TENSIONE'
             AND con.tcon_des = 'ILLUMINAZIONE PUBBLICA'
                THEN 'B'
             ELSE CASE
             WHEN (SELECT attval.catt_valvis
                     FROM v_valcxatt attval
                    WHERE ubi.att_id = attval.att_id
                      AND attval.sta_idsys = 2
                      AND attval.catt_des = 'TIPO FORNITURA EE') =
                                                              'BASSA TENSIONE'
             AND con.tcon_des <> 'DOMESTICO'
             AND con.tcon_des <> 'ILLUMINAZIONE PUBBLICA'
                THEN 'C'
             ELSE CASE
             WHEN (SELECT attval.catt_valvis
                     FROM v_valcxatt attval
                    WHERE ubi.att_id = attval.att_id
                      AND attval.sta_idsys = 2
                      AND attval.catt_des = 'TIPO FORNITURA EE') =
                                                              'MEDIA TENSIONE'
             AND con.tcon_des = 'ILLUMINAZIONE PUBBLICA'
                THEN 'D'
             ELSE CASE
             WHEN (SELECT attval.catt_valvis
                     FROM v_valcxatt attval
                    WHERE ubi.att_id = attval.att_id
                      AND attval.sta_idsys = 2
                      AND attval.catt_des = 'TIPO FORNITURA EE') =
                                                              'MEDIA TENSIONE'
             AND con.tcon_des <> 'DOMESTICO'
             AND con.tcon_des <> 'ILLUMINAZIONE PUBBLICA'
                THEN 'E'
          END
          END
          END
          END
          END tipo_cliente_finale,
          CASE
             WHEN (SELECT MAX (cuf1.vccuf_val)
                     FROM v_valcxcuf cuf1
                    WHERE cuf1.ccon_des = 'RESIDENTE'
                      AND cuf1.cuf_id = ubi.cuf_id
                      AND cuf1.sta_idsys = 2
                      AND cuf1.ver_dtfine = con.ver_dtfine) = 'SI'
             AND con.tcon_des = 'DOMESTICO'
                THEN 'D2'
             ELSE CASE
             WHEN (SELECT MAX (cuf1.vccuf_val)
                     FROM v_valcxcuf cuf1
                    WHERE cuf1.ccon_des = 'RESIDENTE'
                      AND cuf1.cuf_id = ubi.cuf_id
                      AND cuf1.sta_idsys = 2
                      AND cuf1.ver_dtfine = con.ver_dtfine) = 'NO'
             AND con.tcon_des = 'DOMESTICO'
                THEN 'D3'
             ELSE CASE
             WHEN con.tcon_des <> 'DOMESTICO'
                THEN 'ND'
          END
          END
          END tipotariffadom,
          CASE
             WHEN (SELECT MAX (pot.vpxco_val)
                     FROM v_cufot cot, v_valparxcufot pot
                    WHERE cot.cuf_id = ubi.cuf_id
                      AND cot.comser_des = 'VENDITA'
                      AND con.tcon_des = 'DOMESTICO'
                      AND cot.sta_idsys = 02
                      AND pot.cufot_vernum = cot.ver_num
                      AND pot.parot_id = 'DISAGIO_ECON'
                      AND pot.sta_idsys = 02
                      AND pot.ver_dtfine = con.ver_dtfine) <> 'E0'
                THEN 'Y'
             ELSE 'N'
          END tariffa_sociale,
          'ML' tipoapprov, TRUNC (con.con_dtiniges) datadecorrenza,
          TRUNC (con.ver_dtfine) datascadenza,
          (SELECT MAX (att1.vcatt_val)
             FROM v_valcxatt att1
            WHERE att1.att_id = ubi.att_id
              AND att1.ver_dtfine = TO_DATE ('31/12/9999', 'DD/MM/YYYY')
              AND att1.sta_idsys = 02
              AND att1.catt_des = 'POTENZA DISPONIBILE') potdisp,
          (SELECT MAX (att1.vcatt_val)
             FROM v_valcxatt att1
            WHERE att1.att_id = ubi.att_id
              AND att1.ver_dtfine = TO_DATE ('31/12/9999', 'DD/MM/YYYY')
              AND att1.sta_idsys = 02
              AND att1.catt_des = 'POTENZA IMPEGNATA') potimp,
          'Y' contrattosiglato,
          CASE
             WHEN (SELECT MAX (cons.rfaco_des)
                     FROM v_cufot ott, v_voceotcon cons
                    WHERE ott.cuf_id = ubi.cuf_id
                      AND ott.sta_idsys = 2
                      AND ott.ver_dtfine = con.ver_dtfine
                      AND ott.comser_des LIKE '%VENDITA%'
                      AND ott.ot_id = cons.ot_id
                      AND cons.fuco_id = 'ATT') LIKE 'MONORARIA'
                THEN 'M'
             ELSE CASE
             WHEN (SELECT MAX (cons.rfaco_des)
                     FROM v_cufot ott, v_voceotcon cons
                    WHERE ott.cuf_id = ubi.cuf_id
                      AND ott.sta_idsys = 2
                      AND ott.ver_dtfine = con.ver_dtfine
                      AND ott.comser_des LIKE '%VENDITA%'
                      AND ott.ot_id = cons.ot_id
                      AND cons.fuco_id = 'ATT') LIKE '%MULTI%'
                THEN 'T'
             ELSE 'B'
          END
          END tarmonobi
     FROM v_conubifrn ubi,
          v_contratto con,
          v_soggetto sog,
          (SELECT cuf.cuf_id, cuf.ver_dtfine
             FROM v_valcxcuf cuf
            WHERE cuf.ccon_des = 'FOTOVOLTAICO'
              AND cuf.sta_idsys = 2
              AND cuf.vccuf_val = 'SI') aa
    WHERE ubi.az_id = con.az_id
      AND con.ser_id = ubi.ser_id
      AND ubi.cuf_id = aa.cuf_id
      --AND ubi.ver_dtfine = aa.ver_dtfine
      AND ubi.con_anno = con.con_anno
      ---AND UBI.CUF_ID=8777
      AND ubi.con_id = con.con_id
      AND con.sog_id = sog.sog_id)V_UE_GSE
